﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Reflection;
using System.IO;
using Oracle.ManagedDataAccess.Client;
using System.Drawing;
using System.Data.Common;
using Fdb.Model;

namespace Fdb.Oracle
{
    /// <summary>
    /// Oracle数据库操作类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    internal class Manager<T> : DBHandler<T>
    {
        #region 连接字符串

        private OracleDbHelper _OracleDbHelper = new OracleDbHelper();

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="ConnectionString"></param>
        public Manager(string connectionString, DatabaseType databaseType) : base(databaseType)
        {
            _OracleDbHelper.ConnectionString = connectionString;
        }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connectionString"></param>
        public Manager(string connectionString, DatabaseType databaseType, DbTransaction dbTransaction)
            : base(databaseType, dbTransaction)
        {
            _OracleDbHelper.ConnectionString = connectionString;
        }

        #endregion 连接字符串

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dbname"></param>
        public override ExecuteResult CreateDB(string connectionString, string dbname)
        {
            ExecuteResult er = new ExecuteResult();
            return er;
        }

        #region 获取数据库结构抽象方法

        /// <summary>
        /// 获取指定数据库下所有表信息
        /// </summary>
        /// <param name="dbname">数据库名称</param>
        /// <returns></returns>
        public override List<TableClass> GetDbTableList(string dbname)
        {
            List<TableClass> tablelist = new List<TableClass>();
            string sql = "select * from user_tables";
            DataTable dt = this.GetDataTable(sql);
            DataTable tabCommentDT = this.GetDataTable("select * from user_tab_comments");
            foreach (DataRow dr in dt.Rows)
            {
                TableClass tbl = new TableClass();
                tbl.TableName = dr["table_name"].ToString();
                DataRow[] tabCommentArr = tabCommentDT.Select("Table_Name='" + tbl.TableName + "' and Table_Type='TABLE'");
                if (tabCommentArr.Length > 0)
                {
                    tbl.TableCNName = tabCommentArr[0]["comments"].ToString();
                }
                else
                {
                    tbl.TableCNName = tbl.TableName;
                }

                tablelist.Add(tbl);
            }

            return tablelist;
        }

        /// <summary>
        /// 获取指定数据库下指定表的所有字段信息
        /// </summary>
        /// <param name="dbname">数据库名称</param>
        /// <param name="tblname">表名称</param>
        /// <returns></returns>
        public override List<FieldClass> GetTblFieldList(string dbname, string tblname)
        {
            //select * from user_tab_comments 获取表中文名称
            List<FieldClass> fieldList = new List<FieldClass>();
            string sql = "select * from user_tab_columns where Table_Name='" + tblname + "' order by column_id asc";
            DataTable dt = this.GetDataTable(sql);

            string key = "";
            sql = "select * from user_cons_columns where constraint_name = (select constraint_name from user_constraints where table_name = '" + tblname + "' and  constraint_type='P')";
            DataTable keyDT = this.GetDataTable(sql);
            //if (keyDT.Rows.Count > 0)
            //{
            //    key = keyDT.Rows[0]["COLUMN_NAME"].ToString();
            //}

            DataTable filedCommentDT = this.GetDataTable("select * from user_col_comments where table_name='" + tblname + "'");

            foreach (DataRow dr in dt.Rows)
            {
                FieldClass field = new FieldClass();
                field.FieldName = dr["COLUMN_NAME"].ToString();
                DataRow[] filedCommentArr = filedCommentDT.Select("column_name='" + field.FieldName + "'");

                if (filedCommentArr.Length > 0)
                {
                    field.FieldCNName = filedCommentArr[0]["comments"].ToString();
                }
                else
                {
                    field.FieldCNName = field.FieldName;
                }

                field.Nullable = dr["NULLABLE"].ToString().Equals("Y");

                //field.IsPrimaryKey = field.FieldName.Equals(key);
                DataRow[] keyArr=keyDT.Select("COLUMN_NAME='" + field.FieldName+"'");
                if (keyArr.Length > 0)
                {
                    field.IsPrimaryKey = true;
                }
                else
                {
                    field.IsPrimaryKey = false;
                }

                string column_type = dr["DATA_TYPE"].ToString().ToLower();

                if (column_type.IndexOf("number") > -1)
                {
                    field.FieldTypeName = "decimal";
                    if (!field.IsPrimaryKey)
                    {
                        field.FieldTypeName += "?";
                    }
                }
                else if (column_type.IndexOf("float") > -1)
                {
                    field.FieldTypeName = "decimal";
                    if (!field.IsPrimaryKey)
                    {
                        field.FieldTypeName += "?";
                    }
                }
                else if (column_type.IndexOf("char") > -1)
                {
                    field.FieldTypeName = "string";
                }
                else if (column_type.IndexOf("date") > -1)
                {
                    field.FieldTypeName = "DateTime";
                    if (field.Nullable)
                    {
                        field.FieldTypeName += "?";
                    }
                }
                else if (column_type.IndexOf("long raw") > -1)
                {
                    field.FieldTypeName = "System.Drawing.Image";
                }
                else
                {
                    field.FieldTypeName = "object";
                }

                //if (field.IsPrimaryKey)
                //{
                //    field.FieldTypeName = "decimal";//HACK:此处主键类型有待优化
                //}

                fieldList.Add(field);
            }

            return fieldList;
        }

        /// <summary>
        /// 获取数据表结构
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public DataTable GetOleDbSchemaTable(string tableName)
        {
            //获取用户表所有字段SQL
            string sql = "";
            sql += "select t.table_name,";
            sql += "      t.column_name,";
            sql += "      t.data_type,";
            sql += "      t.data_length,";
            sql += "      t.nullable,";
            sql += "      t.column_id,";
            sql += "      c.comments";
            sql += " FROM user_tab_cols t,";
            sql += "      user_col_comments c";
            sql += "      WHERE lower(t.table_name) = '" + _TableName.ToLower() + "'";
            sql += "  and c.table_name = t.table_name";
            sql += "  and c.column_name = t.column_name";
            sql += "  and t.hidden_column = 'NO'";
            sql += "order by t.column_id";

            DataTable dt = this.GetDataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取主键字段
        /// </summary>
        /// <returns></returns>
        public string GetPrimaryKey()
        {
            string sql = string.Empty;
            sql = "select col.column_name ";
            sql += "from user_constraints con,  user_cons_columns col ";
            sql += "where con.constraint_name = col.constraint_name ";
            sql += "and con.constraint_type='P' ";
            sql += "and col.table_name = '" + _TableName + "'";

            object obj = this.GetDataObject(sql);
            if (obj != null)
            {
                return obj.ToString();
            }

            return "GUID";
        }

        /// <summary>
        /// 获取字段属性列表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<FieldClass> GetFieldList(string tableName)
        {
            DataTable table = GetOleDbSchemaTable(tableName);
            DataRow[] allFields = table.Select("1=1");
            List<FieldClass> list = new List<FieldClass>();

            string primaryKey = GetPrimaryKey();
            foreach (DataRow dr in allFields)
            {
                string data_type = dr["data_type"].ToString();
                string c_type = Common.GetOracleCSharpType(data_type);
                if (string.IsNullOrEmpty(c_type)) continue;
                FieldClass field = new FieldClass();
                field.TableType = "Oracle";
                field.TableCNName = dr["table_name"].ToString();
                field.TableName = dr["table_name"].ToString();
                field.FieldCNName = dr["comments"].ToString();
                field.FieldName = dr["column_name"].ToString();
                field.FieldTypeName = dr["data_type"].ToString();
                field.FieldLength = Convert.ToInt32(dr["data_length"].ToString());
                field.Nullable = dr["nullable"].ToString().Equals("Y") ? true : false;
                if (field.FieldName.Equals(primaryKey)) field.IsPrimaryKey = true;
                list.Add(field);
            }

            return list;
        }

        #endregion 获取数据库结构抽象方法

        #region 表维护抽象方法

        /// <summary>
        /// 创建一个默认数据表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult CreateTable(string tableName)
        {
            string sql = "Create TABLE " + tableName + "(";
            sql += "ID INT not null auto_increment primary key";
            sql += ")";
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult ModifyTable(string oldTableName, string newTableName)
        {
            string sql = "ALTER  TABLE " + oldTableName + " RENAME TO " + newTableName;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        ///  新增字段
        /// </summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public override ExecuteResult CreateField(FieldClass field)
        {
            string sql = string.Empty;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="field">field</param>
        /// <returns></returns>
        public override ExecuteResult DeleteField(FieldClass field)
        {
            string sql = "ALTER TABLE " + field.TableName + " DROP COLUMN " + field.FieldName;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult ModifyField(FieldClass oldField, FieldClass newField)
        {
            string sql = string.Empty;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 根据实体类创建表
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult CreateTable()
        {
            string sql = "Create TABLE [" + this._TableName + "](";
            string fields = string.Empty;
            PropertyInfo[] props = GetPropertyInfoArray();
            foreach (PropertyInfo proper in props)
            {
                sql += "[" + proper.Name + "]";
                string fType = string.Empty;
                string type = proper.PropertyType.ToString();

                switch (type)
                {
                    case "System.Int64": fType = "INTEGER"; break;
                    case "System.Int32": fType = "INTEGER"; break;
                    case "System.String": fType = "VARCHAR2"; break;
                    case "System.DateTime": fType = "DATE"; break;
                    case "System.Double": fType = "DECIMAL"; break;
                }

                fType += " ";
                if (!string.IsNullOrEmpty(fields)) fType = "," + fType;

                fields += fType;

            }

            sql += fields;
            sql += ")";

            ExecuteResult er = this.ExecuteSQLWithResult(sql);

            return er;
        }

        /// <summary>
        ///  创建业务流程视图
        /// </summary>
        /// <param name="bizViewName"></param>
        /// <param name="primary"></param>
        /// <param name="datasouceSQL"></param>
        /// <param name="flowCode"></param>
        /// <returns></returns>
        public override ExecuteResult CreateBizFlowView(string flowCode, string bizViewName, string primary, string datasouceSQL)
        {
            ExecuteResult result = new ExecuteResult();
            string str = string.Empty;
            string str2 = "CREATE OR REPLACE VIEW " + bizViewName + " AS select t.*,isnull(o.FlowCode,'" + flowCode + "') as FlowCode,isnull(o.BizRecordId,t." + primary + ") as BizRecordId,isnull(o.BizRecordState,1) as BizRecordState,isnull(o.BizRecordState_Text,'<span style=\"color:green\">未申报</span>') as BizRecordState_Text,isnull(o.CurrentNodeLevel,0) as CurrentNodeLevel,o.ActorId,o.ActorName from ( " + datasouceSQL + ") t ";
            str = str2 + "left join (select * from WF_Current where FlowCode like '" + flowCode + "') o on t." + primary + "=o.BizRecordGuid";
            return this.ExecuteSQLWithResult(str);
        }

        #endregion 表维护抽象方法

        #region 基础方法

        /// <summary>
        /// 开启一个数据库事务对象
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult BeginTransaction()
        {
            ExecuteResult er = new ExecuteResult();
            OracleConnection conn = new OracleConnection(this._OracleDbHelper.ConnectionString);

            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                er.ResultString = "打开数据库连接失败！- " + ex.ToString();
                er.Success = false;
                return er;
            }

            if (conn.State == ConnectionState.Open)
            {
                try
                {
                    er.DbTransaction = conn.BeginTransaction();
                    er.DbConnection = conn;
                }
                catch (Exception ex)
                {
                    er.ResultString = "开启事务失败！- " + ex.ToString();
                    er.Success = false;
                    return er;
                }

                return er;
            }
            else
            {
                er.ResultString = "打开连接失败！";
                er.Success = false;
                return er;
            }
        }

        /// <summary>
        /// 测试连接
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult TestConnnection()
        {
            ExecuteResult er = new ExecuteResult();
            OracleConnection conn = new OracleConnection(_OracleDbHelper.ConnectionString);

            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                if (ex.ToString().IndexOf("ORA-01017") > -1)
                {
                    er.ResultString = "ORA-01017无效的用户名和密码！";
                    er.Success = false;
                    return er;
                }
            }

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                return er;
            }
            else
            {
                er.ResultString = "打开连接失败！";
                er.Success = false;
                return er;
            }
        }

        /// <summary>
        /// 获得datarow
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataRow GetDataRow(string sql)
        {
            DataTable dt = new DataTable();

            try
            {
                dt = GetDataTable(sql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt.Rows.Count == 0 ? null : dt.Rows[0];
        }

        /// <summary>
        /// 获得datarow
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override DataRow GetDataRow(string sql, NHashtable ht)
        {
            DataTable dt = new DataTable();

            try
            {
                dt = GetDataTable(sql, ht);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt.Rows.Count == 0 ? null : dt.Rows[0];
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql)
        {
            DataSet dataSet = new DataSet();

            try
            {
                _OracleDbHelper.ExecuteDataTable(sql,null, ref dataSet);
            }

            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql, NHashtable ht)
        {
            DataSet dataSet = new DataSet();
            OracleParameter[] parlist = null;
            try
            {
                parlist = this.GetDbParameterArray(ht);
            }
            catch (Exception ex)
            {
                throw new Exception("构造参数时：GetDataTable(sql=" + sql + ",ht长度=" + (ht!=null?ht.Count:0) + ")。详情：" + ex.ToString());
            }

            try
            {
                _OracleDbHelper.ExecuteDataTable(sql, parlist, ref dataSet);
            }

            catch (Exception ex)
            {
                throw new Exception("执行SQL时：GetDataTable(sql=" + sql + ",ht长度=" + (ht != null ? ht.Count : 0) + ")。详情：" + ex.ToString());
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 根据存储过程获取DataTable对象
        /// </summary>
        /// <param name="storeProcName">存储过程名称</param>
        /// <returns></returns>
        public override DataTable GetDataTableWithStoreProc(string storeProcName)
        {
            return GetDataTableWithStoreProc(storeProcName, null);
        }

        /// <summary>
        /// 根据存储过程获取DataTable对象
        /// </summary>
        /// <param name="storeProcName">存储过程名称</param>
        /// <param name="ht">参数</param>
        /// <returns></returns>
        public override DataTable GetDataTableWithStoreProc(string storeProcName, NHashtable ht)
        {
            DataTable dataTable = new DataTable();
            OracleParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                dataTable = _OracleDbHelper.RunProcedureDatatable(storeProcName, parlist);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dataTable;
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithResult(string sql)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            try
            {
                _OracleDbHelper.ExecuteSQL(sql);
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithConn(string conn, string sql)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                _OracleDbHelper.ConnectionString = conn;
                _OracleDbHelper.ExecuteSQL(sql);
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行带参数的sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithResult(string sql, NHashtable ht)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                OracleParameter[] parlist = this.GetDbParameterArray(ht);
                _OracleDbHelper.ExecuteSQL(sql, parlist);
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override ExecuteResult ExcuteTransactionSQL(string sql, NHashtable ht)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                OracleParameter[] parlist = this.GetDbParameterArray(ht);
                long id = _OracleDbHelper.ExcuteTransactionSQL(sql, parlist);
                if (id > 0)
                {
                    er.ID = (int)id;
                }
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 获得单个object变量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override object GetDataObject(string sql)
        {
            object o = null;

            try
            {
                o = this.GetDataObject(sql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return o;

        }

        /// <summary>
        /// 获得单个object变量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override object GetDataObject(string sql, NHashtable ht)
        {
            OracleParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                object obj = _OracleDbHelper.ExecuteScalar(sql, parlist);
                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取分页查询结果
        /// </summary>
        /// <param name="pageIndex">分页号</param>
        /// <returns></returns>
        public override DataTable GetDataTable(PagedQuery pagedQuery)
        {
            //SELECT * FROM   
            //(  
            //SELECT A.*, ROWNUM RN   
            //FROM (SELECT * FROM PC_CUSTOMER_BASE) A   
            //WHERE ROWNUM <= 20  
            //)  
            //WHERE RN >= 1 

            int minNum = (pagedQuery.PageIndex - 1) * pagedQuery.PageSize + 1;
            int maxNum = pagedQuery.PageIndex * pagedQuery.PageSize;

            string sql = "select * from ";
            sql += "(";
            sql += "SELECT A.*, ROWNUM RN ";
            sql += "FROM (SELECT * FROM " + pagedQuery.TableName;

            if (!string.IsNullOrEmpty(pagedQuery.TMPWhereClause))
            {
                sql += " where " + pagedQuery.TMPWhereClause;
            }

            string orderby = " order by " + (string.IsNullOrEmpty(pagedQuery.SortClause) ? (this._KeyProper.Name+" asc") : pagedQuery.SortClause);
            sql += orderby;

            sql += ") A ";
            sql += "WHERE ROWNUM <= " + maxNum;
            sql += ") ";
            sql += "WHERE RN >= " + minNum;


            DataTable dt = new DataTable();

            try
            {
                dt = this.GetDataTable(sql, pagedQuery.TMPParamList);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <returns></returns>
        public override int GetCount(PagedQuery pagedQuery)
        {
            string sql = "select Count(*) from " + pagedQuery.TableName + (string.IsNullOrEmpty(pagedQuery.TMPWhereClause) ? string.Empty : (" where " + pagedQuery.TMPWhereClause));
            object o = null;

            try
            {
                o = this.GetDataObject(sql, pagedQuery.TMPParamList);
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override int RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            object o = null;

            try
            {
                o = this._OracleDbHelper.RunProcedure(storedProcName, parameters);
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion 基础方法

        #region 增删改方法

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <returns></returns>
        public override ExecuteResult Insert(string tableName, NHashtable ht)
        {
            string sql = "insert into " + tableName + "(";
            string realSql = string.Empty;

            int i = 0;
            foreach (string str in ht.Keys)
            {
                sql += str;
                if (i < ht.Count - 1) sql += ",";
                i++;
            }

            sql += ")";
            sql += " values(";
            realSql = sql;

            i = 0;
            string realVal = string.Empty;//真实值
            foreach (string str in ht.Keys)
            {
                sql += ":" + str;
                realVal = Common.GetRealVal(ht[str]);
                realSql += realVal;
                if (i < ht.Count - 1)
                {
                    sql += ",";
                    realSql += ",";
                }

                i++;
            }

            sql += ")";
            realSql += ")";

            ExecuteResult er = new ExecuteResult();
            

            try
            {
                er.Success = true;
                er = this.ExecuteSQLWithResult(sql, ht);
                er.IsAdd = true;
                er.Sql = realSql;
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Insert(T t)
        {
            int id = 0;
            ExecuteResult er = new ExecuteResult();
            NHashtable nht = new NHashtable();
            nht = CreateContentValues(t);

            PropertyInfo key = Common.GetFirstDbKeyField<T>();
            if (key != null)
            {
                bool isUseWithDescription = Common.IsUseWithDescription(key);
                if (isUseWithDescription)
                {
                    string seq = Common.GetDescription(key);
                    if (!string.IsNullOrEmpty(seq))
                    {
                        object o = this.GetDataObject("select " + seq + ".nextval FROM dual");//TODO:要标注成功返回和失败返回。
                        if (o != null)
                        {
                            nht[key.Name] = decimal.Parse(o.ToString());//更新主键为序列值
                            id = int.Parse(o.ToString());
                        }
                    }
                }
            }

            er = this.Insert(this._TableName, nht);//此处SQLITE中必须设置ID为Intger自增长类型，否则报错
            er.ID = id;
            return er;
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="condition">条件</param>
        /// <param name="fieldTypes">所有字段类型</param>
        /// <param name="fieldSizes">所有字段大小</param>
        /// <param name="realWhere">实际where语句</param>
        /// <returns></returns>
        public ExecuteResult Update(string tableName, NHashtable ht, string where, string realWhere)
        {
            string sql = "update " + tableName + " set ";
            string realSql = sql;

            int i = 0;
            foreach (string str in ht.Keys)
            {
                if (i == ht.Count - 1) break;//HACK：此处有待优化。
                sql += str + "=:" + str;
                realSql += str + "=" + Common.GetRealVal(ht[str]);
                if (i < ht.Count - 2)
                {
                    sql += ",";
                    realSql += ",";
                }

                i++;
            }

            sql += " " + where;
            if (string.IsNullOrEmpty(realWhere))
            {
                realSql = sql;
            }
            else
            {
                realSql += " " + realWhere;
            }

            ExecuteResult er = new ExecuteResult();

            try
            {
                er = this.ExecuteSQLWithResult(sql, ht);
                er.IsAdd = false;
                er.Sql = realSql;
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public override ExecuteResult Update(string tableName, NHashtable ht, string where)
        {
            return Update(tableName, ht, where, string.Empty);
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Update(T t)
        {
            if (this._KeyProper == null)
            {
                return new ExecuteResult { ResultString = "未找到主键！", Success = false };
            }

            NHashtable nht = new NHashtable();
            nht = CreateContentValues(t);
            string keyProperType = this._KeyProper.PropertyType.ToString().ToLower();
            object keyProperValue = this._KeyProper.GetValue(t, null);
            string hashkey = this._KeyProper.Name + "_" + DateTime.Now.ToString("HHmmssfff");//此处可以优化，出现和已有的参数名重复可能性很低
            nht.Add(hashkey, keyProperValue);
            return this.Update(this._TableName, nht, "Where " + this._KeyProper.Name + " = :" + hashkey, "Where " + this._KeyProper.Name + " = " + Common.GetRealVal(keyProperValue));
        }

        /// <summary>
        /// 根据编号删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(long id)
        {
            string sql = "Delete from " + this._TableName + " Where " + this._KeyProper.Name + " = " + id;
            return this.ExecuteSQLWithResult(sql, null);
        }

        /// <summary>
        /// 根据GUID删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(Guid guid)
        {
            string sql = "Delete from " + this._TableName + " Where guid = '" + guid + "'";
            return this.ExecuteSQLWithResult(sql, null);
        }

        /// <summary>
        /// 删除指定对象
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(T t)
        {
            string sql = "Delete from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;
            return this.ExecuteSQLWithResult(sql, paramtable);
        }

        #endregion 增删改方法

        #region 参数构造方法

        /// <summary>
        /// 获得数据库参数数组
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        private OracleParameter[] GetDbParameterArray(NHashtable ht)
        {
            OracleParameter[] parlist = null;
            if (ht != null && ht.Count > 0)
            {
                parlist = new OracleParameter[ht.Count];
                int i = 0;

                foreach (string str in ht.Keys)
                {
                    OracleDbType oracleType = Common.ChangeToOracleType(ht[str]);
                    int size = ht[str] == null ? 0 : ht[str].ToString().Length;
                    object value = ht[str] == null ? DBNull.Value : ht[str];

                    if (oracleType == OracleDbType.LongRaw)
                    {
                        if (value != DBNull.Value)
                        {
                            //byte[] buff = ImageHelper.ImageToBytes((Image)value);
                            //size = buff.Length;
                            //value = buff;
                        }
                    }

                    OracleParameter oleDbParameter = _OracleDbHelper.CreateInParam(":" + str, oracleType, size, value);
                    parlist[i] = oleDbParameter;
                    i++;
                }
            }

            return parlist;
        }

        #endregion 参数构造方法

        #region 泛型查询方法

        /// <summary>
        /// 根据编号获取单个实例
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override T Fetch(long id)
        {
            //NHashtable nht = new NHashtable();
            //object id = GetPropertyValue(t, "ID");
            //nht.Add("ID", id);
            return Fetch("select * from " + this._TableName + " where " + this._KeyProper.Name + "=" + id);
        }

        /// <summary>
        /// 根据GUID获取单个实例
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override T Fetch(Guid guid)
        {
            return Fetch("select * from " + this._TableName + " where guid='" + guid.ToString() + "'");
        }

        /// <summary>
        /// 根据sql获取实例对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override T Fetch(string sql)
        {
            return Fetch(sql, null);
        }

        /// <summary>
        /// 根据sql和查询条件获取实例
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="nht"></param>
        /// <returns></returns>
        public override T Fetch(string sql, NHashtable nht)
        {
            DataRow dr = this.GetDataRow(sql, nht);
            return Fetch(dr);
        }

        /// <summary>
        /// 根据实体对象获取列表
        /// </summary>
        /// <returns></returns>
        public override T Fetch(T t)
        {
            List<T> list = FetchList(t);
            if (list.Count > 0) return list[0];

            return default(T);
        }

        /// <summary>
        /// 获取全部实例对象列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList()
        {
            string sql = "select * from " + this._TableName;
            return FetchList(sql, null);
        }

        /// <summary>
        /// 根据sql获取实例对象列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override List<T> FetchList(string sql)
        {
            return FetchList(sql, null);
        }

        /// <summary>
        /// 根据sql和参数表获取实例对象列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override List<T> FetchList(string sql, NHashtable ht)
        {
            List<T> list = new List<T>();
            DataTable dt = this.GetDataTable(sql, ht);
            if (dt == null) return list;

            foreach (DataRow dr in dt.Rows)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 获取全部实例对象列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList(T t)
        {
            string sql = "select * from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;
            return FetchList(sql, paramtable);
        }

        /// <summary>
        /// 将DataRow数组转为List
        /// </summary>
        /// <param name="drs"></param>
        /// <returns></returns>
        public override List<T> FetchList(DataRow[] drs)
        {
            List<T> list = new List<T>();
            foreach (DataRow dr in drs)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 将DataTable类型转为List
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private List<T> FetchList(DataTable dt)
        {
            List<T> list = new List<T>();
            if (dt == null) return list;

            foreach (DataRow dr in dt.Rows)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 根据实体对象获取列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList(T t, string whereClause)
        {
            string sql = "select * from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where))
            {
                where = " where " + where;
            }

            if (!string.IsNullOrEmpty(whereClause))
            {
                if (string.IsNullOrEmpty(where))
                {
                    where = " where ";
                }
                else
                {
                    where += " and ";
                }
            }

            where += whereClause;
            sql += where;
            return FetchList(sql, paramtable);
        }

        /// <summary>
        /// 获取分页结果
        /// </summary>
        /// <param name="t"></param>
        /// <param name="pagedQuery"></param>
        /// <returns></returns>
        public override List<T> FetchPageList(T t, PagedQuery pagedQuery)
        {
            this.InitPagedQuery(t, pagedQuery);
            DataTable dt = GetDataTable(pagedQuery);
            return FetchList(dt);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="t"></param>
        /// <param name="pagedQuery"></param>
        /// <returns></returns>
        public override int FetchCount(T t, PagedQuery pagedQuery)
        {
            this.InitPagedQuery(t, pagedQuery);
            int count = GetCount(pagedQuery);
            return count;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override int FetchCount(T t)
        {
            string sql = "select Count(*) from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;

            object obj = this.GetDataObject(sql, paramtable);
            int count = Common.ConvertToIntWithMinValue(obj);
            return count;
        }

        #endregion 泛型查询抽象方法
    }
}